﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.SqlClient;
using VideoClassLibrary.Model;
using System.Data.Common;

namespace VideoClassLibrary.BusinessLogicLayer
{
    public class CourseListModule
    {
        /// <summary>
        /// 获取学生的课程表列表
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="schoolId"></param>
        /// <param name="strWhere"></param>
        /// <returns>add by zhangqian</returns>
        public DataSet GetUserCourseList(int userId, int schoolId, string strWhere, string orderByField)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  c.ID as CourseID, c.VC_name,b.ID,b.VCL_startDate,b.VCL_startTime,b.VCL_EndDate,b.VCL_EndTime,b.VCL_desc,b.teacherID,b.RoomID,(Select VR_roomName From  v_room Where Id=b.RoomID) as RoomName, a.UserID,a.SchoolID,a.TrainClassID,(Select Vtc_name From v_trainClass Where ID=a.TrainClassID ) as Vtc_name   ");
            strSql.AppendLine("  FROM V_courselistDest a  join V_courselist  b on  a.TrainClassID=b.trainClassID    ");
            strSql.AppendLine(" join V_course  c  on b.courseID=c.ID   ");
            strSql.Append(" where  a.UserID=@UserID and a.SchoolID=@SchoolID  ");
            if (strWhere != "")
            {
                strSql.Append(strWhere);
            }
            if (orderByField != "")
            {

                strSql.Append("  order by  " + orderByField);
            }
            Database db = VDataBase.CreateDatabase();
            DbCommand command = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(command, "UserID", DbType.Int32, userId);
            db.AddInParameter(command, "SchoolID", DbType.Int32, schoolId);
            return db.ExecuteDataSet(command);
        }

        



        public DataSet GetCourseByID(int ID, int schoolId, string strWhere, string orderByField)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select distinct b.ID,RoomID,Vtc_name, VR_roomName,VC_name,VCL_startTime,VCL_EndTime,VR_roomName ,VCL_startDate,VCL_EndDate ");
            strSql.AppendLine("  FROM V_courselist  b   ");
            strSql.AppendLine(" left join V_room  r on b.RoomID=r.ID    ");
            strSql.Append(" left join V_course v on v.ID=b.courseID ");
            strSql.Append(" left join v_trainClass vt on b.trainClassID=vt.ID  ");
            strSql.Append(" where    b.SchoolID=@SchoolID and vt.id=@ID ");
            if (strWhere != "")
            {
                strSql.Append(strWhere);
            }
            if (orderByField != "")
            {

                strSql.Append("  order by  " + orderByField);
            }
            Database db = VDataBase.CreateDatabase();
            DbCommand command = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(command, "ID", DbType.Int32, ID);
            db.AddInParameter(command, "SchoolID", DbType.Int32, schoolId);
            return db.ExecuteDataSet(command);
        }

        public int ChkCourse(string className, string classRoom, string classTeacher, string classBeginTime, string classEndTime, string classBeginDate, string classEndDate, string classCourse)
        {

            Database db = VDataBase.CreateDatabase();
            StringBuilder sb=new StringBuilder();
            StringBuilder sbm=new StringBuilder();
            sb.Append("SELECT * FROM V_courselist where 1=1");
            if (!string.IsNullOrEmpty(className) && !string.IsNullOrEmpty(classBeginDate) && !string.IsNullOrEmpty(classBeginTime) && !string.IsNullOrEmpty(classEndDate) && !string.IsNullOrEmpty(classEndTime))
            {
                sb.Append(" and trainClassID=" + className);
                sb.Append(" and VCL_startDate='" + classBeginDate + "'");
                sb.Append(" and VCL_startTime='" + classBeginTime + "'");
                sb.Append(" and VCL_EndDate='" + classEndDate + "'");
                sb.Append(" and VCL_EndTime='" + classEndTime + "'");


            }
            if (!string.IsNullOrEmpty(classTeacher) && !string.IsNullOrEmpty(classBeginDate) && !string.IsNullOrEmpty(classBeginTime) && !string.IsNullOrEmpty(classEndDate) && !string.IsNullOrEmpty(classEndTime))
            {
                sb.Append(" and teacherID=" + classTeacher);
                sb.Append(" and VCL_startDate='" + classBeginDate + "'");
                sb.Append(" and VCL_startTime='" + classBeginTime + "'");
                sb.Append(" and VCL_EndDate='" + classEndDate + "'");
                sb.Append(" and VCL_EndTime='" + classEndTime + "'");
            }
            //if (!string.IsNullOrEmpty(className) && !string.IsNullOrEmpty(classTeacher) && !string.IsNullOrEmpty(classRoom) && !string.IsNullOrEmpty(classCourse) && !string.IsNullOrEmpty(classBeginDate) && !string.IsNullOrEmpty(classBeginTime) && !string.IsNullOrEmpty(classEndDate) && !string.IsNullOrEmpty(classEndTime))
            //{
            //    sb.Append(" and trainClassID=" + className);
            //    sb.Append(" and RoomID=" + classRoom);
            //    sb.Append(" and teacherID=" + classTeacher);
            //    sb.Append(" and courseID=" + classCourse);
            //    sb.Append(" and VCL_startDate='" + classBeginDate + "'");
            //    sb.Append(" and VCL_startTime='" + classBeginTime + "'");
            //    sb.Append(" and VCL_EndDate='" + classEndDate + "'");
            //    sb.Append(" and VCL_EndTime='" + classEndTime + "'");


            //}
            //if (!string.IsNullOrEmpty(classRoom))
            //{
            //    sb.Append(" and RoomID=" + classRoom);

            //}
          
            //if (!string.IsNullOrEmpty(classCourse))
            //{
            //    sb.Append(" and courseID=" + classCourse);
            //}

            

            DbCommand command = db.GetSqlStringCommand(sb.ToString());
            DataSet dataSet= db.ExecuteDataSet(command);
           if(dataSet.Tables[0].Rows.Count>0)
           {
               return 1;

           }
            return -1;
        }



        /// <summary>
        /// 查询所有课程列表
        /// </summary>
        /// <param name="schoolId">学校ID</param>
        /// <returns></returns>
        public DataSet QueryAllCourseListBySchoolId(int schoolId, string courseid)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.ID,VS_name,VR_roomName,VC_name,VT_realname = ISNULL(VT_realname,''),VTC_Number,VTC_name,vcl_startTime,vcl_endTime,vcl_startDate,vcl_EndDate ");
            strSql.Append("from  dbo.V_courselist a join dbo.V_school b on a.SchoolID=b.ID ");
            strSql.Append("join dbo.V_room c on a.RoomID=c.ID ");
            strSql.Append("left join dbo.V_course d on a.courseID=d.ID ");
            strSql.Append("left join V_trainClass vt on a.trainClassID=vt.id ");
            //strSql.Append("join dbo.V_teacher t on 1=1 ");
            strSql.Append("left join dbo.V_teacher t on t.ID =a.teacherID ");
            strSql.Append(" where b.Id=@schoolId");
            if (courseid == null)
            {
                strSql.Append("");
            }
            else
            {

                strSql.Append(" and d.id=" + courseid);
            }

            strSql.Append(" order by[id] desc");
            Database db = VDataBase.CreateDatabase();
            DbCommand command = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(command, "schoolId", DbType.Int32, schoolId);
            return db.ExecuteDataSet(command);

        }
        /// <summary>
        /// 增加一条或多条课程表
        /// </summary>
        public int AddCourseList(V_courselist model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strTotalSql = new StringBuilder();

            #region 插入数据的sql语句模板
            strSql.Append("insert into V_courselist(");
            strSql.Append("SchoolID,RoomID,TrainClassId,teacherID,courseID,VCL_startTime,VCL_EndTime,VCL_desc,VCL_startDate,VCL_EndDate");
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append("" + model.SchoolID + ",");
            strSql.Append("" + model.RoomID + ",");
            strSql.Append(""+model.TrainClassId+",");
            strSql.Append("" + model.teacherID + ",");
            strSql.Append("" + model.courseID + ",");
            strSql.Append("'" + model.VCL_startTime + "',");
            strSql.Append("'" + model.VCL_EndTime + "',");
            strSql.Append("'" + model.VCL_desc + "',");
            strSql.Append("'@Start',");
            strSql.Append("'@End'");
            strSql.Append(") ;   ");
            #endregion

            #region  循环每一天，一条插入语句
            TimeSpan ts = model.VCL_EndDate.Subtract(model.VCL_startDate);
            if (ts.Days > 0)//结束日期比开始日期晚
            {
                DateTime dt = new DateTime();
                for (int i = 0; i <= ts.Days; i++)
                {
                    dt = model.VCL_startDate.AddDays(i);
                    strTotalSql.Append(strSql.ToString().Replace("@Start", dt.ToString()).
              Replace("@End", dt.ToString()));
                }
            }
            else
            {
                strTotalSql.Append(strSql.ToString().Replace("@Start", model.VCL_startDate.ToString()).
               Replace("@End", model.VCL_startDate.ToString()));
            }
            #endregion
            Database db = VDataBase.CreateDatabase();
            int result = 0;
            result = db.ExecuteNonQuery(CommandType.Text, strTotalSql.ToString());
            return result;
        }
        /// <summary>
        /// 根据课程表ID删除数据
        /// </summary>
        /// <param name="CID"></param>
        /// <returns></returns>
        public int DeleteCourseList(int cID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from v_courselist where ID=@courselistID");
            Database db = VDataBase.CreateDatabase();
            DbCommand command = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(command, "courselistID", DbType.Int32, cID);
            int result = db.ExecuteNonQuery(command);
            if (result > 0)
            { return 1; }
            else
            { return -1; }

        }

        /// <summary>
        /// 根据课程表ID删除数据
        /// </summary>
        /// <param name="CID"></param>
        /// <returns></returns>
        public int DeleteCourseList(string ids)
        {
            int result = 0;
            string[] idd = ids.Split(',');
            for (int i = 0; i < idd.Length; i++)
            {
                
             

                StringBuilder strSql = new StringBuilder();
                strSql.Append("delete from v_courselist where ID=@id");
                Database db = VDataBase.CreateDatabase();
                DbCommand command = db.GetSqlStringCommand(strSql.ToString());
                db.AddInParameter(command, "id", DbType.Int32, idd[i]);

                try
                {
                   result +=db.ExecuteNonQuery(command);
                }
                catch (Exception)
                {
                    
                    throw;
                }
               
                
            }


            if (result > 0)
            { return 1; }
            return -1;
        }
        /// <summary>
        /// 添加课程表信息，重复添加的数据跳过
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddCourseListByProcedure(V_courselist model)
        {
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("Proc_CourseList_Insert");

            db.AddInParameter(dbCommand, "SchoolID", DbType.Int32, model.SchoolID);
            db.AddInParameter(dbCommand, "RoomID", DbType.Int32, model.RoomID);
            db.AddInParameter(dbCommand, "trainClassID", DbType.Int32, model.TrainClassId);
            db.AddInParameter(dbCommand, "teacherID", DbType.Int32, model.teacherID);
            db.AddInParameter(dbCommand, "courseID", DbType.Int32, model.courseID);
            db.AddInParameter(dbCommand, "VCL_startTime", DbType.String, model.VCL_startTime);
            db.AddInParameter(dbCommand, "VCL_EndTime", DbType.String, model.VCL_EndTime);
            db.AddInParameter(dbCommand, "VCL_desc", DbType.AnsiString, model.VCL_desc);
            db.AddInParameter(dbCommand, "VCL_startDate", DbType.String, model.VCL_startDate);
            db.AddInParameter(dbCommand, "VCL_EndDate", DbType.String, model.VCL_EndDate);
            try
            {
                db.ExecuteNonQuery(dbCommand);
            }
            catch
            {

                return false;
            }
            return true;

        }

        /// <summary>
        /// 根据学校id获取所有课程名称
        /// </summary>
        /// <param name="schoolid">学校ID</param>
        /// <returns></returns>
        public DataSet QueryByCourseNamebySchoolid(int schoolid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,vc_name from v_course where schoolid=" + schoolid);
            Database db = VDataBase.CreateDatabase();
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql.ToString());
            return ds;

        }

        /// <summary>
        /// 根据时间段查询
        /// </summary>
        /// <param name="starDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public DataSet QueryByCourseBeginDate(int schoolId,int courseId,DateTime starDate, DateTime endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select ");
            strSql.Append(" * ");
            strSql.Append(" from ");
            strSql.Append(" dbo.V_courselist a join dbo.V_school b on a.SchoolID=b.ID ");
            strSql.Append(" join dbo.V_room c on a.RoomID=c.ID ");
            strSql.Append(" join dbo.V_course d on a.courseID=d.ID ");
            strSql.Append(" join dbo.V_teacher t on a.teacherID=t.ID ");
            strSql.Append(" join V_trainClass vt on a.trainClassID=vt.ID ");
            strSql.Append("where b.Id=" + schoolId);
            if (courseId > 0)
            {
                strSql.Append(" AND d.ID =" + courseId);
            }
            strSql.Append(" and vcl_startdate between '" + starDate + "' and '" + endDate + "' order by vcl_startdate ");
            Database db = VDataBase.CreateDatabase();
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql.ToString());
            return ds;
        }

        /// <summary>
        /// 根据时间段查询
        /// </summary>
        /// <param name="starDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public DataSet QueryByCourseBeginDate(int id, DateTime starDate, DateTime endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            strSql.Append("a.ID,VS_name,VR_roomName,VC_name,VT_realname,vcl_startTime,vcl_endTime,vcl_startDate,vcl_EndDate ");
            strSql.Append("from ");
            strSql.Append("dbo.V_courselist a join dbo.V_school b on a.SchoolID=b.ID ");
            strSql.Append("join dbo.V_room c on a.RoomID=c.ID ");
            strSql.Append("join dbo.V_course d on a.courseID=d.ID ");
            strSql.Append("join dbo.V_teacher t on a.teacherID=t.ID ");
            strSql.Append("where b.Id=" + id);
            strSql.Append(" and vcl_startdate between '" + starDate + "' and '" + endDate + "'");
            Database db = VDataBase.CreateDatabase();
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql.ToString());
            return ds;
        }


        /// <summary>
        /// 更新一条数据
        /// </summary>
        public void UpdateCourseList(V_courselist model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update V_courselist set ");
            strSql.Append("SchoolID=@SchoolID,");
            strSql.Append("RoomID=@RoomID,");
            strSql.Append("trainClassID=@trainClassID,");
            strSql.Append("teacherID=@teacherID,");
            strSql.Append("courseID=@courseID,");
            strSql.Append("VCL_startTime=@VCL_startTime,");
            strSql.Append("VCL_EndTime=@VCL_EndTime,");
            strSql.Append("VCL_desc=@VCL_desc,");
            strSql.Append("VCL_startDate=@VCL_startDate,");
            strSql.Append("VCL_EndDate=@VCL_EndDate");
            strSql.Append(" where ID=@ID ");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "ID", DbType.Int32, model.ID);
            db.AddInParameter(dbCommand, "SchoolID", DbType.Int32, model.SchoolID);
            db.AddInParameter(dbCommand, "RoomID", DbType.Int32, model.RoomID);
            db.AddInParameter(dbCommand, "trainClassID", DbType.Int32, model.TrainClassId);
            db.AddInParameter(dbCommand, "teacherID", DbType.Int32, model.teacherID);
            db.AddInParameter(dbCommand, "courseID", DbType.Int32, model.courseID);
            db.AddInParameter(dbCommand, "VCL_startTime", DbType.String, model.VCL_startTime);
            db.AddInParameter(dbCommand, "VCL_EndTime", DbType.String, model.VCL_EndTime);
            db.AddInParameter(dbCommand, "VCL_desc", DbType.AnsiString, model.VCL_desc);
            db.AddInParameter(dbCommand, "VCL_startDate", DbType.String, model.VCL_startDate);
            db.AddInParameter(dbCommand, "VCL_EndDate", DbType.String, model.VCL_EndDate);
            db.ExecuteNonQuery(dbCommand);

        }
        /// <summary>
        /// 查询数据修改
        /// </summary>
        /// <param name="id">课程表id</param>
        /// <param name="sid">学校id</param>
        /// <returns></returns>
        public DataSet QueryAllCourseListById(int id, int sid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.ID ,[RoomID],a.[trainClassID],[teacherID],[courseID],VS_name,VR_roomName,VTC_name,VC_name,VTC_Number,VT_realname,vcl_startTime,vcl_endTime,vcl_startDate,vcl_EndDate ");
            strSql.Append("from  dbo.V_courselist a join dbo.V_school b on a.SchoolID=b.ID ");
            strSql.Append("join dbo.V_room c on a.RoomID=c.ID ");
            strSql.Append("left join dbo.V_course d on a.courseID=d.ID ");
            strSql.Append("left join dbo.V_teacher t on a.teacherID=t.ID ");
            strSql.Append("join v_trainclass tr on a.trainClassID=tr.ID ");
            strSql.Append("where a.Id=@id and b.id=@sid");

            strSql.Append(" order by[id] desc");
            Database db = VDataBase.CreateDatabase();
            DbCommand command = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(command, "sid", DbType.Int32, sid);
            db.AddInParameter(command, "id", DbType.Int32, id);
            return db.ExecuteDataSet(command);

        }

       /// <summary>
       /// 同一时段不能给同一培训班安排课程，如果教师被安排，同一时段不能将同一老师安排到不同教室。
       /// </summary>
       /// <param name="classroomId"></param>
       /// <param name="trainClassId"></param>
       /// <param name="teacherId"></param>
       /// <param name="startDate"></param>
       /// <param name="startTime"></param>
       /// <param name="endTime"></param>
       /// <returns></returns>
        public bool IsArrangement(int classroomId, int trainClassId, int teacherId, DateTime startDate, string startTime, string endTime,int days)
        {
            for (int i = 0; i < days; i++)
            {
                if (teacherId == 0)
                {
                    // 同一时段不能给同一培训班安排课程
                    StringBuilder strSql = new StringBuilder();
                    strSql.Append("");
                }
                else
                {
                    //同一时段不能将同一老师安排到不同教室

                }
            }
            return false;
        }

        /// <summary>
        /// 判断是否在相同时间内有重复教室上课
        /// </summary>
        public DataSet IfNameExist(string roomName,DateTime starDate, string startime,string course)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.ID ,[RoomID],VR_roomName,vcl_startTime,VC_name,vcl_endTime,vcl_startDate,vcl_EndDate from ");
            strSql.Append("dbo.V_courselist a join dbo.V_room c on a.RoomID=c.ID ");
            strSql.Append("join dbo.V_course d on a.courseID=d.ID ");
            strSql.Append(" where VR_roomName=@roomname and vcl_startDate=@startDate  and vcl_startTime=@startime and VC_name=@course");
            
            Database db = VDataBase.CreateDatabase();
            DbCommand command = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(command,"roomname",DbType.String,roomName);
            db.AddInParameter(command, "startime", DbType.String, startime);
            db.AddInParameter(command, "startDate", DbType.DateTime, starDate);
            db.AddInParameter(command,"course",DbType.String,course);
            DataSet ds = db.ExecuteDataSet(command);
           int result= ds.Tables[0].Rows.Count;
           if (result != 0)
            {
                return db.ExecuteDataSet(command);

            }
            else
                return null;

        }

        public DataSet GetCourseDetailByDestId(int schoolId, int courseId)
        {
            string sql = " select top 1 vc_name,vt_realname,vc_desc   FROM V_courselist a join v_course b on a.courseID=b.ID JOIN dbo.V_teacher t ON a.teacherID=t.id  where a.SchoolID="
                + schoolId + " and a.ID=" + courseId;
            Database db = VDataBase.CreateDatabase();
            DbCommand command = db.GetSqlStringCommand(sql);
            return db.ExecuteDataSet(command);
        }

    }
}
